﻿Imports DTO
Imports System.Data.OleDb
Public Class ThamSoDAO
    Public Shared Function LayDanhSach() As List(Of ThamSoDTO)
        Dim ds As New List(Of ThamSoDTO)
        'B1 & B2: Tao chuoi ket noi, mo ket noi bang doi tuong ket noi
        Dim cn As OleDbConnection
        cn = Data_Provider.ConnecDTB()
        'B3: Tao chuoi strSQL thao tac CSDL
        Dim strSQL As String
        strSQL = "Select * From THUOC"
        Dim cmd As New OleDbCommand(strSQL, cn)
        Dim dr As OleDbDataReader
        dr = cmd.ExecuteReader()
        While (dr.Read())
            Dim hsDto As New ThamSoDTO
            hsDto.SoCachDung = dr("SoCachDung")
            hsDto.SoDonVi = dr("SoDonVi")
            hsDto.SoLoaiBenh = dr("SoLoaiBenh")
            hsDto.SoLuongKham = dr("SoLuongKham")
            hsDto.TienKham = dr("TienKham")
            ds.Add(hsDto)
        End While
        'B5: Dong ket noi CSDL
        dr.Close()
        cn.Close()
        Return ds
    End Function
    Public Shared Function SuaThamSo(ByVal ts As ThamSoDTO) As DataTable
        Dim dt As New DataTable
        Dim ketnoi As OleDbConnection
        ketnoi = Data_Provider.ConnecDTB()
        Dim lenh As OleDbCommand
        Dim sql As String = "UPDATE THAMSO SET TienKham=" & ts.TienKham & " ,  SoLuongKham = " & ts.SoLuongKham & " ,  SoLoaiBenh = " & ts.SoLoaiBenh & ", SoDonVi =" & ts.SoDonVi & ", SoCachDung = " & ts.SoCachDung
        lenh = New OleDbCommand(sql, ketnoi)
        Dim da As OleDbDataAdapter
        da = New OleDbDataAdapter(sql, ketnoi)
        da.Fill(dt)
        ketnoi.Close()

        Return dt
    End Function


    Public Shared Function LayBang() As DataTable
        Dim dt As New DataTable
        Dim cn As OleDbConnection
        'B1 & B2: Tao chuoi ket noi, mo ket noi bang doi tuong ket noi
        cn = Data_Provider.ConnecDTB()
        'B3: Tao chuoi strSQL thao tac CSDL
        Dim strSQL As String
        strSQL = "Select * "
        strSQL = strSQL & " From THAMSO "
        'B4: Thuc thi chuoi strSQL
        Dim da As OleDbDataAdapter
        da = New OleDbDataAdapter(strSQL, cn)
        da.Fill(dt)
        'B5: Dong ket noi CSDL
        cn.Close()
        Return dt

    End Function

End Class
